from PyQt6.QtWidgets import (QApplication, QMainWindow, QWidget, QTableView, 
                            QVBoxLayout, QHBoxLayout, QPushButton, QMessageBox,
                            QLabel, QLineEdit, QComboBox, QFormLayout)
from PyQt6.QtSql import (QSqlDatabase, QSqlQuery, QSqlQueryModel, 
                        QSqlRelationalTableModel, QSqlRelation, QSqlRelationalDelegate)
from PyQt6.QtCore import Qt, QSize
import sys

class StudentManagementSystem(QMainWindow):
    def __init__(self):
        super().__init__()
        self.setWindowTitle("学生信息管理系统")
        self.resize(1000, 700)
        
        # 初始化UI
        self.initUI()
        
        # 初始化数据库
        if not self.initDB():
            QMessageBox.critical(self, "错误", "数据库连接失败，应用将退出")
            sys.exit(1)
            
        # 初始化数据模型
        self.initModels()
        
        # 设置默认视图
        self.showQueryModelView()
    
    def initUI(self):
        """初始化用户界面"""
        # 主窗口部件
        self.centralWidget = QWidget()
        self.setCentralWidget(self.centralWidget)
        
        # 主布局
        self.mainLayout = QHBoxLayout()
        self.centralWidget.setLayout(self.mainLayout)
        
        # 左侧控制面板
        self.controlPanel = QWidget()
        self.controlPanel.setFixedWidth(250)
        self.controlLayout = QVBoxLayout()
        self.controlPanel.setLayout(self.controlLayout)
        
        # 右侧数据显示区
        self.dataDisplay = QWidget()
        self.dataLayout = QVBoxLayout()
        self.dataDisplay.setLayout(self.dataLayout)
        
        # 添加部件到主布局
        self.mainLayout.addWidget(self.controlPanel)
        self.mainLayout.addWidget(self.dataDisplay)
        
        # 初始化控制面板
        self.initControlPanel()
        
        # 初始化数据显示区
        self.initDataDisplay()
    
    def initControlPanel(self):
        """初始化控制面板"""
        # 数据库操作按钮
        self.btnRawQuery = QPushButton("执行原始查询")
        self.btnRawQuery.clicked.connect(self.executeRawQuery)
        
        self.btnQueryModel = QPushButton("查询模型视图")
        self.btnQueryModel.clicked.connect(self.showQueryModelView)
        
        self.btnRelationalModel = QPushButton("关系模型视图")
        self.btnRelationalModel.clicked.connect(self.showRelationalModelView)
        
        # 查询条件输入
        self.queryCondition = QLineEdit()
        self.queryCondition.setPlaceholderText("输入查询条件，如: age > 20")
        
        # 排序选择
        self.sortCombo = QComboBox()
        self.sortCombo.addItems(["按ID升序", "按ID降序", "按年龄升序", "按年龄降序"])
        
        # 添加部件到控制面板
        self.controlLayout.addWidget(QLabel("数据库操作:"))
        self.controlLayout.addWidget(self.btnRawQuery)
        self.controlLayout.addWidget(self.btnQueryModel)
        self.controlLayout.addWidget(self.btnRelationalModel)
        self.controlLayout.addSpacing(20)
        
        self.controlLayout.addWidget(QLabel("查询条件:"))
        self.controlLayout.addWidget(self.queryCondition)
        self.controlLayout.addWidget(QLabel("排序方式:"))
        self.controlLayout.addWidget(self.sortCombo)
        
        # 添加学生表单
        self.addStudentForm = QWidget()
        formLayout = QFormLayout()
        self.addStudentForm.setLayout(formLayout)
        
        self.stuNameEdit = QLineEdit()
        self.stuGenderCombo = QComboBox()
        self.stuGenderCombo.addItems(["男", "女"])
        self.stuAgeEdit = QLineEdit()
        self.stuMajorCombo = QComboBox()
        
        formLayout.addRow("姓名:", self.stuNameEdit)
        formLayout.addRow("性别:", self.stuGenderCombo)
        formLayout.addRow("年龄:", self.stuAgeEdit)
        formLayout.addRow("专业:", self.stuMajorCombo)
        
        self.btnAddStudent = QPushButton("添加学生")
        self.btnAddStudent.clicked.connect(self.addStudent)
        
        self.controlLayout.addSpacing(20)
        self.controlLayout.addWidget(QLabel("添加学生:"))
        self.controlLayout.addWidget(self.addStudentForm)
        self.controlLayout.addWidget(self.btnAddStudent)
        
        self.controlLayout.addStretch()
    
    def initDataDisplay(self):
        """初始化数据显示区"""
        # 创建表格视图
        self.tableView = QTableView()
        self.tableView.setSelectionBehavior(QTableView.SelectionBehavior.SelectRows)
        self.tableView.setSelectionMode(QTableView.SelectionMode.SingleSelection)
        
        # 状态标签
        self.statusLabel = QLabel("就绪")
        self.statusLabel.setAlignment(Qt.AlignmentFlag.AlignCenter)
        
        # 添加到布局
        self.dataLayout.addWidget(self.tableView)
        self.dataLayout.addWidget(self.statusLabel)
    
    def initDB(self):
        """初始化数据库连接"""
        # 创建一个新的数据库连接
        self.db = QSqlDatabase.addDatabase('QODBC')
        
        # 配置连接参数
        connection_string = (
            "DRIVER={MySQL ODBC 9.3 Unicode Driver};"  # 确保这个驱动名称正确
            "SERVER=localhost;"
            "DATABASE=student_management;"
            "USER=root;"
            "PASSWORD=936580;"  # 替换为你的MySQL密码
            "CHARSET=utf8mb4;"
        )
        
        self.db.setDatabaseName(connection_string)
        
        if not self.db.open():
            self.statusLabel.setText(f"数据库连接失败: {self.db.lastError().text()}")
            return False
        
        # 检查表是否存在，不存在则创建
        query = QSqlQuery()
        if not query.exec("SHOW TABLES LIKE 'student'"):
            self.statusLabel.setText(f"检查表失败: {query.lastError().text()}")
            return False
            
        if not query.next():  # 表不存在，创建表
            self.createTables()
        
        return True
    
    def createTables(self):
        """创建数据库表"""
        queries = [
            "CREATE TABLE major ("
            "  major_id INT AUTO_INCREMENT PRIMARY KEY,"
            "  major_name VARCHAR(100) NOT NULL,"
            "  department VARCHAR(100)"
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
            
            "CREATE TABLE student ("
            "  ID INT AUTO_INCREMENT PRIMARY KEY,"
            "  stu_name VARCHAR(100) NOT NULL,"
            "  gender ENUM('男', '女') NOT NULL,"
            "  age INT,"
            "  major_id INT,"
            "  FOREIGN KEY (major_id) REFERENCES major(major_id)"
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
            
            "INSERT INTO major (major_name, department) VALUES "
            "('计算机科学', '计算机学院'),"
            "('软件工程', '计算机学院'),"
            "('信息安全', '网络空间安全学院'),"
            "('人工智能', '人工智能学院')",
            
            "INSERT INTO student (stu_name, gender, age, major_id) VALUES "
            "('张三', '男', 20, 1),"
            "('李四', '女', 22, 2),"
            "('王五', '男', 21, 1),"
            "('赵六', '女', 23, 3)"
        ]
        
        for sql in queries:
            query = QSqlQuery()
            if not query.exec(sql):
                self.statusLabel.setText(f"创建表失败: {query.lastError().text()}")
                return False
                
        return True
    
    def initModels(self):
        """初始化所有数据模型"""
        # 1. 原始查询模型
        self.queryModel = QSqlQueryModel()
        
        # 2. 关系模型
        self.relationalModel = QSqlRelationalTableModel()
        self.relationalModel.setTable("student")
        self.relationalModel.setRelation(4, QSqlRelation("major", "major_id", "major_name"))
        
        # 设置表头
        headers = ["学号", "姓名", "性别", "年龄", "专业"]
        for i, header in enumerate(headers):
            self.relationalModel.setHeaderData(i, Qt.Orientation.Horizontal, header)
        
        # 3. 初始化专业下拉框
        self.updateMajorComboBox()
    
    def updateMajorComboBox(self):
        """更新专业下拉框数据"""
        self.stuMajorCombo.clear()
        
        query = QSqlQuery("SELECT major_id, major_name FROM major")
        while query.next():
            self.stuMajorCombo.addItem(query.value(1), query.value(0))
    
    def executeRawQuery(self):
        """执行原始SQL查询"""
        query = QSqlQuery()
        sql = "SELECT s.ID, s.stu_name, s.gender, s.age, m.major_name " \
              "FROM student s LEFT JOIN major m ON s.major_id = m.major_id"
        
        # 添加查询条件
        condition = self.queryCondition.text().strip()
        if condition:
            sql += f" WHERE {condition}"
        
        # 添加排序
        sort_text = self.sortCombo.currentText()
        if sort_text == "按ID升序":
            sql += " ORDER BY s.ID ASC"
        elif sort_text == "按ID降序":
            sql += " ORDER BY s.ID DESC"
        elif sort_text == "按年龄升序":
            sql += " ORDER BY s.age ASC"
        elif sort_text == "按年龄降序":
            sql += " ORDER BY s.age DESC"
        
        if query.exec(sql):
            self.queryModel.setQuery(query)
            
            # 设置表头
            headers = ["学号", "姓名", "性别", "年龄", "专业"]
            for i, header in enumerate(headers):
                self.queryModel.setHeaderData(i, Qt.Orientation.Horizontal, header)
            
            self.tableView.setModel(self.queryModel)
            self.tableView.resizeColumnsToContents()
            self.statusLabel.setText(f"查询成功，返回 {self.queryModel.rowCount()} 条记录")
        else:
            self.statusLabel.setText(f"查询失败: {query.lastError().text()}")
    
    def showQueryModelView(self):
        """显示查询模型视图"""
        self.queryModel.setQuery("SELECT * FROM student")
        
        # 设置表头
        headers = ["学号", "姓名", "性别", "年龄", "专业ID"]
        for i, header in enumerate(headers):
            self.queryModel.setHeaderData(i, Qt.Orientation.Horizontal, header)
        
        self.tableView.setModel(self.queryModel)
        self.tableView.resizeColumnsToContents()
        self.statusLabel.setText("切换到查询模型视图")
    
    def showRelationalModelView(self):
        """显示关系模型视图"""
        # 应用过滤条件
        condition = self.queryCondition.text().strip()
        if condition:
            self.relationalModel.setFilter(condition)
        else:
            self.relationalModel.setFilter("")
        
        # 应用排序
        sort_text = self.sortCombo.currentText()
        if sort_text == "按ID升序":
            self.relationalModel.setSort(0, Qt.SortOrder.AscendingOrder)
        elif sort_text == "按ID降序":
            self.relationalModel.setSort(0, Qt.SortOrder.DescendingOrder)
        elif sort_text == "按年龄升序":
            self.relationalModel.setSort(3, Qt.SortOrder.AscendingOrder)
        elif sort_text == "按年龄降序":
            self.relationalModel.setSort(3, Qt.SortOrder.DescendingOrder)
        
        # 重新选择数据
        self.relationalModel.select()
        
        # 设置委托使专业列显示下拉框
        delegate = QSqlRelationalDelegate(self.tableView)
        self.tableView.setItemDelegateForColumn(4, delegate)
        
        self.tableView.setModel(self.relationalModel)
        self.tableView.resizeColumnsToContents()
        self.statusLabel.setText("切换到关系模型视图")
    
    def addStudent(self):
        """添加新学生"""
        # 获取表单数据
        name = self.stuNameEdit.text().strip()
        gender = self.stuGenderCombo.currentText()
        age = self.stuAgeEdit.text().strip()
        major_index = self.stuMajorCombo.currentIndex()
        major_id = self.stuMajorCombo.itemData(major_index)
        
        # 验证数据
        if not name:
            QMessageBox.warning(self, "警告", "请输入学生姓名")
            return
            
        try:
            age = int(age)
            if age <= 0:
                raise ValueError
        except ValueError:
            QMessageBox.warning(self, "警告", "请输入有效的年龄")
            return
        
        # 使用事务确保数据一致性
        self.db.transaction()
        
        try:
            # 方法1: 使用QSqlQuery
            query = QSqlQuery()
            query.prepare("INSERT INTO student (stu_name, gender, age, major_id) VALUES (?, ?, ?, ?)")
            query.addBindValue(name)
            query.addBindValue(gender)
            query.addBindValue(age)
            query.addBindValue(major_id)
            
            if not query.exec():
                raise Exception(f"插入失败: {query.lastError().text()}")
            
            # 方法2: 使用关系模型 (替代方案)
            # record = self.relationalModel.record()
            # record.setValue("stu_name", name)
            # record.setValue("gender", gender)
            # record.setValue("age", age)
            # record.setValue("major_id", major_id)
            # if not self.relationalModel.insertRecord(-1, record):
            #     raise Exception(f"插入失败: {self.relationalModel.lastError().text()}")
            # if not self.relationalModel.submitAll():
            #     raise Exception(f"提交失败: {self.relationalModel.lastError().text()}")
            
            self.db.commit()
            
            # 清空表单
            self.stuNameEdit.clear()
            self.stuAgeEdit.clear()
            self.stuGenderCombo.setCurrentIndex(0)
            self.stuMajorCombo.setCurrentIndex(0)
            
            # 刷新视图
            if self.tableView.model() == self.relationalModel:
                self.showRelationalModelView()
            else:
                self.showQueryModelView()
                
            self.statusLabel.setText(f"成功添加学生: {name}")
            
        except Exception as e:
            self.db.rollback()
            QMessageBox.critical(self, "错误", str(e))
            self.statusLabel.setText(f"添加学生失败: {str(e)}")

if __name__ == "__main__":
    app = QApplication(sys.argv)
    
    # 设置应用程序样式
    app.setStyle("Fusion")
    
    # 创建主窗口
    window = StudentManagementSystem()
    window.show()
    
    sys.exit(app.exec())